//
//  DataAnalyzer.m
//  MarketDataAnalyzer
//
//  Created by jiro on 10/01/18.
//  Copyright 2010 jiro music. All rights reserved.
//

#import "DataAnalyzer.h"
#import "DbInfo.h"
#import "../DbTypes/DbConstants.h"

@implementation DataAnalyzer

- (id) initWithConnectionToDb: (NSString*) dbName ofHost: (NSString*) host asUser: (NSString*) userName andDbInfo: (DbInfo*) dbInfo_
{
	self = [ super init ];

	dbOperator = [ [ DbOperator alloc ] init ];
	if ( NO == [ dbOperator connectToDb: dbName ofHost: host asUser: userName ] )
	{
		errorType = DataAnalyzerError_DbConnection;
		errorMessage = [ dbOperator errorMessage ];
		return NO;
	}
	dbInfo = dbInfo_;
	errorType = DataAnalyzerError_None;

	return self;
}

- (DbOperator*) dbOperator
{
	return dbOperator;
}

- (NSString*) errorMessage
{
	NSString* ret = @"";
	
	switch ( errorType ) {
	case DataAnalyzerError_DbConnection:
		ret = [ ret stringByAppendingString: @"DBへの接続に失敗\n" ];
		break;
	case DataAnalyzerError_InvalidOperationToField:
		ret = [ ret stringByAppendingString: @"フィールドに対する不正な操作" ];
		break;
	case DataAnalyzerError_FailtoExecuteSqlQuery:
		ret = [ ret stringByAppendingString: @"SQLクエリの実行に失敗" ];
		break;
	case DataAnalyzerError_None:
		ret = [ ret stringByAppendingString: @"エラーなし" ];
		break;
	}
	ret = [ ret stringByAppendingFormat: @"%@", errorMessage ];
	return ret;
}

-(void) disconnect
{
	[ dbOperator disconnect ];
}

- (BOOL) isNumericType: (DbDataType) type 
{
	if ( type == DbData_Real || type == DbData_Integer || type == DbData_Money )
	{
		return YES;
	}
	return NO;
}

- (BOOL) isDbDataTypeNeedQuotion: (DbDataType) type
{
	if ( type == DbData_String || type == DbData_Date )
	{
		return YES;
	}
	return NO;
}

- (BOOL) validateAsNumericDataAnalyzeArg: (DataAnalyzeArg*) arg
{
	if ( [ arg dataFieldId ] != Field_Invalid )
	{
		FieldInfo* dataField = [ dbInfo fieldInfoOfFieldId: [ arg dataFieldId ] ofTableId: [ arg tableId ] ];
		if ( dataField == nil || NO == [ self isNumericType: [ dataField dbDataType ] ] )
		{
			return NO;
		}

		if ( [ arg conditionFieldId ] != Field_Invalid )
		{
			FieldInfo* conditionField = [ dbInfo fieldInfoOfFieldId: [ arg conditionFieldId ] ofTableId: [ arg tableId ] ];
			if ( conditionField == nil )
			{
				return NO;
			}
		}
	}

	if ( [ arg rangeConditionFieldId ] != Field_Invalid )
	{
		FieldInfo* rangeConditionField = [ dbInfo fieldInfoOfFieldId: [ arg rangeConditionFieldId ] ofTableId: [ arg tableId ] ];
		if ( rangeConditionField == nil )
		{
			return NO;
		}
	}

	return YES;
}

- (NSString*) createSqlQueryTo: (DataAnalyzeArg*) arg named: (NSString*) functionName
{
	FieldInfo* conditionFieldInfo = [ dbInfo fieldInfoOfFieldId: [ arg conditionFieldId ] ofTableId: [ arg tableId ] ];
	FieldInfo* rangeConditionFieldInfo = [ dbInfo fieldInfoOfFieldId: [ arg rangeConditionFieldId ] ofTableId: [ arg tableId ] ];

	NSString* conditionString;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ conditionFieldInfo dbDataType ] ] ) {
		conditionString = [ NSString stringWithFormat: @"%@", [ arg condition ] ];
	} else {
		conditionString = [ NSString stringWithFormat: @"'%@'", [ arg condition ] ];
	}

	NSString* fromConditionString;
	NSString* toConditionString;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ rangeConditionFieldInfo dbDataType ] ] ) {
		fromConditionString = [ NSString stringWithFormat: @"%@", [ arg fromCondition ] ];
		toConditionString = [ NSString stringWithFormat: @"%@", [ arg toCondition ] ];
	} else {
		fromConditionString = [ NSString stringWithFormat: @"'%@'", [ arg fromCondition ] ];
		toConditionString = [ NSString stringWithFormat: @"'%@'", [ arg toCondition ] ];		
	}

	FieldInfo* dataFieldInfo = [ dbInfo fieldInfoOfFieldId: [ arg dataFieldId ] ofTableId: [ arg tableId ] ];
	NSString* dataFieldName = nil;
	if ( nil == dataFieldInfo ) {
		dataFieldName = [ arg.dataFieldNode toString ];
	}
	else {
		dataFieldName = [ dataFieldInfo name ];
	}

	NSString* tableName = [ [ dbInfo tableInfoOfTableId: [ arg tableId ] ] name ]; 
	NSString* conditionFieldName = [ conditionFieldInfo name ];
	NSString* rangeConditionFieldName = [ rangeConditionFieldInfo name ];
	if ( [ functionName isEqualToString: @"count" ] )
	{
		return
			[ NSString stringWithFormat: @"select count(*) from %@ where %@ = %@ and %@ <= %@ and %@ <= %@;",
				functionName, dataFieldName, tableName, conditionFieldName, conditionString, 
				fromConditionString, rangeConditionFieldName, rangeConditionFieldName, toConditionString ];
	}
	else
	{
		return
			[ NSString stringWithFormat: @"select %@( %@ ) from %@ where %@ = %@ and %@ <= %@ and %@ <= %@;",
				functionName, dataFieldName, tableName, conditionFieldName, conditionString, 
				fromConditionString, rangeConditionFieldName, rangeConditionFieldName, toConditionString ];
	}
}

- (NSString*) createSqlQueryTo: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId named: (NSString*) functionName
{
	FieldInfo* dataFieldInfo1 = [ dbInfo fieldInfoOfFieldId: [ arg1 dataFieldId ] ofTableId: [ arg1 tableId ] ];
	FieldInfo* conditionFieldInfo1 = [ dbInfo fieldInfoOfFieldId: [ arg1 conditionFieldId ] ofTableId: [ arg1 tableId ] ];
	FieldInfo* rangeConditionFieldInfo1 = [ dbInfo fieldInfoOfFieldId: [ arg1 rangeConditionFieldId ] ofTableId: [ arg1 tableId ] ];

	FieldInfo* dataFieldInfo2 = [ dbInfo fieldInfoOfFieldId: [ arg2 dataFieldId ] ofTableId: [ arg2 tableId ] ];
	FieldInfo* conditionFieldInfo2 = [ dbInfo fieldInfoOfFieldId: [ arg2 conditionFieldId ] ofTableId: [ arg2 tableId ] ];
	FieldInfo* rangeConditionFieldInfo2 = [ dbInfo fieldInfoOfFieldId: [ arg2 rangeConditionFieldId ] ofTableId: [ arg2 tableId ] ];

	FieldInfo* joinFieldInfo = [ dbInfo fieldInfoOfFieldId: joinFieldId ofTableId: [ arg1 tableId ] ];
	NSString* conditionString1;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ conditionFieldInfo1 dbDataType ] ] ) {
		conditionString1 = [ NSString stringWithFormat: @"%@", [ arg1 condition ] ];
	} else {
		conditionString1 = [ NSString stringWithFormat: @"'%@'", [ arg1 condition ] ];
	}
	NSString* fromConditionString1;
	NSString* toConditionString1;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ rangeConditionFieldInfo1 dbDataType ] ] ) {
		fromConditionString1 = [ NSString stringWithFormat: @"%@", [ arg1 fromCondition ] ];
		toConditionString1 = [ NSString stringWithFormat: @"%@", [ arg1 toCondition ] ];
	} else {
		fromConditionString1 = [ NSString stringWithFormat: @"'%@'", [ arg1 fromCondition ] ];
		toConditionString1 = [ NSString stringWithFormat: @"'%@'", [ arg1 toCondition ] ];		
	}
	
	NSString* conditionString2;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ conditionFieldInfo2 dbDataType ] ] ) {
		conditionString2 = [ NSString stringWithFormat: @"%@", [ arg2 condition ] ];
	} else {
		conditionString2 = [ NSString stringWithFormat: @"'%@'", [ arg2 condition ] ];
	}
	NSString* fromConditionString2;
	NSString* toConditionString2;
	if ( NO == [ self isDbDataTypeNeedQuotion: [ rangeConditionFieldInfo2 dbDataType ] ] ) {
		fromConditionString2 = [ NSString stringWithFormat: @"%@", [ arg2 fromCondition ] ];
		toConditionString2 = [ NSString stringWithFormat: @"%@", [ arg2 toCondition ] ];
	} else {
		fromConditionString2 = [ NSString stringWithFormat: @"'%@'", [ arg2 fromCondition ] ];
		toConditionString2 = [ NSString stringWithFormat: @"'%@'", [ arg2 toCondition ] ];		
	}
	
	NSString* dataFieldName1;
	NSString* dataFieldName2;
	if ( nil == dataFieldInfo1 ) {
		dataFieldName1 = [ arg1.dataFieldNode toStringWithValuePrefix: @"table_a." ];
	}
	else {
		dataFieldName1 = [ NSString stringWithFormat: @"table_a.%@" , [ dataFieldInfo1 name ] ];
	}
	if ( nil == dataFieldInfo2 ) {
		dataFieldName2 = [ arg1.dataFieldNode toStringWithValuePrefix: @"table_b." ];
	}
	else {
		dataFieldName2 = [ NSString stringWithFormat: @"table_b.%@" , [ dataFieldInfo2 name ] ];
	}

	if ( [ functionName isEqualToString: @"count" ] )
	{
		return [ NSString stringWithFormat: 
			@"select count(*) from"
			@"  (select * from %@ where %@ = %@ and %@ <= %@ and %@ <= %@ ) as table_a inner join"
			@"  (select * from %@ where %@ = %@ and %@ <= %@ and %@ <= %@ ) as table_b"
			@"	on table_a.%@ = table_b.%@;",
			[ [ dbInfo tableInfoOfTableId: [ arg1 tableId ] ] name ],
			[ conditionFieldInfo1 name ], conditionString1, fromConditionString1, [ rangeConditionFieldInfo1 name ], [ rangeConditionFieldInfo1 name ], toConditionString1,
			[ [ dbInfo tableInfoOfTableId: [ arg2 tableId ] ] name ],
			[ conditionFieldInfo2 name ], conditionString2, fromConditionString2, [ rangeConditionFieldInfo2 name ], [ rangeConditionFieldInfo2 name ], toConditionString2,
			[ joinFieldInfo name ], [ joinFieldInfo name ] ];
	}
	else
	{
		return [ NSString stringWithFormat: 
			@"select %@( %@, %@ ) from"
			@"  (select * from %@ where %@ = %@ and %@ <= %@ and %@ <= %@ ) as table_a inner join"
			@"  (select * from %@ where %@ = %@ and %@ <= %@ and %@ <= %@ ) as table_b"
			@"	on table_a.%@ = table_b.%@;",
			functionName, dataFieldName1, dataFieldName2,
			[ [ dbInfo tableInfoOfTableId: [ arg1 tableId ] ] name ],
			[ conditionFieldInfo1 name ], conditionString1, fromConditionString1, [ rangeConditionFieldInfo1 name ], [ rangeConditionFieldInfo1 name ], toConditionString1,
			[ [ dbInfo tableInfoOfTableId: [ arg2 tableId ] ] name ],
			[ conditionFieldInfo2 name ], conditionString2, fromConditionString2, [ rangeConditionFieldInfo2 name ], [ rangeConditionFieldInfo2 name ], toConditionString2,
			[ joinFieldInfo name ], [ joinFieldInfo name ] ];
	}
}

- (BOOL) executeSingleReturnNumericValueSqlQuery: (NSString*) sqlQuery into: (double*) value
{
	errorMessage = sqlQuery;	//	for debug

	PGresult* result = nil;
	DbResponseType response = [ dbOperator executeQuery: sqlQuery intoResult: & result ];
	BOOL ret;
	switch ( response ) {
	case DbResponse_Success:
	case DbResponse_SuccessWithWarning:
		ret = YES;
		break;
	case DbResponse_Fail:
	case DbResponse_Unknown:
	default:
		ret = NO;
		break;
	}

	//	---- 結果を処理 ---- //
	if ( YES == ret ) {
		*value = atof( PQgetvalue( result, 0, 0 ) );
	} else {
		errorType = DataAnalyzerError_FailtoExecuteSqlQuery;
		errorMessage = 
			[ NSString stringWithFormat: @"error message: %s\n sql query:%@",
				PQresultErrorMessage( result ), sqlQuery ];
	}
	//	------------------ //
	
	//	---- 後始末 ---- //
	PQclear( result );
	//	--------------- //

	return ret;
}

- (BOOL) countOf: (DataAnalyzeArg*) arg intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg named: @"count" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) countOf: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg1 ] || NO == [ self validateAsNumericDataAnalyzeArg: arg2 ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg1 arg: arg2 andJoinFieldId: joinFieldId named: @"count" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}


- (BOOL) averageOf: (DataAnalyzeArg*) arg intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg named: @"avg" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) varianceOf: (DataAnalyzeArg*) arg intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg named: @"var_samp" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) standardDeviationOf: (DataAnalyzeArg*) arg intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg named: @"stddev_samp" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) covarianceOf: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg1 ] || NO == [ self validateAsNumericDataAnalyzeArg: arg2 ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg1 arg: arg2 andJoinFieldId: joinFieldId named: @"covar_samp" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) correlationCoefficientOf: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg1 ] || NO == [ self validateAsNumericDataAnalyzeArg: arg2 ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	NSString* sqlQuery = [ self createSqlQueryTo: arg1 arg: arg2 andJoinFieldId: joinFieldId named: @"corr" ];
	return [ self executeSingleReturnNumericValueSqlQuery: sqlQuery into: value ];
}

- (BOOL) regressionCoefficientOf: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg1 ] || NO == [ self validateAsNumericDataAnalyzeArg: arg2 ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	double covariance;
	double variance;
	if ( NO == [ self covarianceOf: arg1 arg: arg2 andJoinFieldId: joinFieldId intoValue: & covariance ] )
	{
		return NO;
	}
	if ( NO == [ self varianceOf: arg1 intoValue: & variance ] )
	{
		return NO;
	}
	*value = covariance / variance;
	return YES;
}

- (BOOL) intersectionOf: (DataAnalyzeArg*) arg1 arg: (DataAnalyzeArg*) arg2 andJoinFieldId: (int) joinFieldId intoValue: (double*) value
{
	if ( NO == [ self validateAsNumericDataAnalyzeArg: arg1 ] || NO == [ self validateAsNumericDataAnalyzeArg: arg2 ] )
	{
		errorType = DataAnalyzerError_InvalidOperationToField;
		return NO;
	}
	double regressionCoefficient;
	double average1;
	double average2;
	if ( NO == [ self regressionCoefficientOf: arg1 arg: arg2 andJoinFieldId: joinFieldId intoValue: & regressionCoefficient ] ||
		 NO == [ self averageOf: arg1 intoValue: & average1 ] ||
		 NO == [ self averageOf: arg2 intoValue: & average2 ] )
	{
		return NO;
	}
	*value = average2 - regressionCoefficient * average1;
	return YES;
}

@end
